package org.example.db;

import org.example.model.Gender;
import org.example.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 这是一个查询类，与数据库直接交互的代码都写在这里
 * All the code that directly interact with the database is here
 * @author Alex
 * @version 1.0
 * @since 1.0
 */
public class Query {
	static String url = "jdbc:sqlite:D:\\IdeaProjects\\WebService_Project\\Server\\database\\student.sqlite3";
	
	/**
	 *
	 * @param id ID of the student you want to query.
	 *              你想查询的学生的id
	 * @return the student whose id is {@code id}. id为{@code id}的学生
	 */
	static public Student getStudentByID(String id){
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = "select id, name_cn, name_fr, class, gender from Student where Student.id = ?";
		Student student = new Student();
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(url);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,id);
			resultSet=preparedStatement.executeQuery();
			while (resultSet.next()) {
				int i = 1;
				while (true) {
					try {
						String s = resultSet.getString(i);
						System.out.printf("%s\t", s);
						++i;
					} catch (Exception e) {
						break;
					}
				}
				System.out.println();
				student.setId(resultSet.getString(1));
				student.setNameCN(resultSet.getString(2));
				student.setNameFR(resultSet.getString(3));
				student.setClassName(resultSet.getString(4));
				student.setGender(Gender.valueOf(resultSet.getString(5)));
				
			}
		} catch (ClassNotFoundException | SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (preparedStatement!=null){
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
		return student;
	}
	
	/**
	 *
	 * @return A list containing all students in the database.
	 * 一个列表，包含数据库中所有的学生。
	 */
	static public List<Student> getAllStudents(){
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = "select id, name_cn, name_fr, class, gender from Student";
		
		List<Student> allStudents= new ArrayList<>();
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(url);
			preparedStatement=connection.prepareStatement(sql);
			
			resultSet=preparedStatement.executeQuery();
			while (resultSet.next()) {
				/*int i = 1;
				while (true) {
					try {
						String s = resultSet.getString(i);
						System.out.printf("%s\t", s);
						++i;
					} catch (Exception e) {
						break;
					}
				}
				System.out.println();
				*/
				Student student = new Student();
				student.setId(resultSet.getString(1));
				student.setNameCN(resultSet.getString(2));
				student.setNameFR(resultSet.getString(3));
				student.setClassName(resultSet.getString(4));
				student.setGender(Gender.valueOf(resultSet.getString(5)));
				allStudents.add(student);
				
			}
		} catch (ClassNotFoundException | SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (preparedStatement!=null){
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
		return allStudents;
	}
	
	/**
	 *
	 * @param student Students you want to add.你想添加的学生
	 * @return the {@code student} you passed in
	 */
	static public Student addStudent(Student student){
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = "INSERT INTO Student (id, name_cn, name_fr, class, gender) VALUES (?,?,?,?,?)";
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(url);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,student.getId());
			preparedStatement.setString(2,student.getNameCN());
			preparedStatement.setString(3,student.getNameFR());
			preparedStatement.setString(4,student.getClassName());
			preparedStatement.setString(5,student.getGender().toString());
			int result = preparedStatement.executeUpdate();
			System.out.printf("有%d行记录被修改\n",result);
			
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (preparedStatement!=null){
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
		return student;
	}
	
	/**
	 *
	 * @param id {@code id} of the student you want to delete你想删除的学生的id。
	 * @return Number of rows of affected data in the database.数据库中受影响数据的行数
	 */
	static public int deleteStudent(String id){
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = "DELETE FROM Student WHERE id = ?";
		int result=0;
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(url);
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,id);
			result = preparedStatement.executeUpdate();
			System.out.printf("有%d行记录被修改\n",result);
			
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (preparedStatement!=null){
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
		return result;
	}
}
